package net.lh.contactsserver.utils;

import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

public class ExcelHelper {
	
	public static String getCellValue(Sheet sheet, int rownum, int colnum) {
    	Row row = sheet.getRow(rownum);
    	String result = "";
    	if (row != null) {
    		Cell cell = row.getCell(colnum);
    		if (row != null) {
    			result = getCellValue(cell);
    		}    		
    	}
    	
    	return result;
    }
	
	/**
	 * 
	 * 根据Cell类型获取数值
	 * @param cell
	 * @return
	 */
    public static String getCellValue(Cell cell) {
        String cellvalue = "";
        if (cell != null) {
            // 判断当前Cell的Type
            switch (cell.getCellType()) {
            // 如果当前Cell的Type为NUMERIC
            case Cell.CELL_TYPE_NUMERIC:{
            	short format = cell.getCellStyle().getDataFormat();  
                // 判断当前的cell是否为Date
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    // 如果是Date类型则，转化为Data格式
                    //方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
                    //cellvalue = cell.getDateCellValue().toLocaleString();
                    //方法2：这样子的data格式是不带带时分秒的：2011-10-12
                	 double d = cell.getNumericCellValue();   
                     Date date = HSSFDateUtil.getJavaDate(d);
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    cellvalue = sdf.format(date);
                }else if(format == 180||format == 181||format == 182||format == 184 || format == 185 || format == 186 || format == 187||format == 188||
                		format == 190 || format == 191 || format == 192 || format == 193|| format == 194 || 
                		format == 195 || format == 196|| format == 197|| format == 198|| format == 199|| format == 200){  
                	        //特殊的日期格式，根据单元格的id来判断其是否为日期格式让进行格式化
                			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  
                	        double value = cell.getNumericCellValue();  
	                   	    Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);  
	                   	    cellvalue = sdf.format(date);  
            	}
                // 如果是纯数字
                else {
                    // 取得当前Cell的数值。使用BigDecimal避免出现科学计数法字符串
                	DecimalFormat df = new DecimalFormat("#0");
                	cellvalue=df.format(cell.getNumericCellValue());
                }
                break;
            }
            case Cell.CELL_TYPE_FORMULA: {
            	
            	try {
		       		 DecimalFormat df = new DecimalFormat("#0.0000");
		       		 cell.setCellType(Cell.CELL_TYPE_NUMERIC); //设置其单元格类型为数字  	
		       		 cellvalue = String.valueOf(df.format(cell.getNumericCellValue())); //获取数字值
				} catch (Exception e) {
					 cellvalue="";
				}
                break;
            }
            // 如果当前Cell的Type为STRIN
            case Cell.CELL_TYPE_STRING:
                // 取得当前的Cell字符串
                cellvalue = cell.getRichStringCellValue().getString();
                break;
            // 默认的Cell值
            default:
                cellvalue = "";
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;
    }

}
    

